CREATE OR REPLACE TRIGGER oradev.trace_trg
BEFORE CREATE OR ALTER OR DROP ON DATABASE
WHEN (ora_dict_obj_owner!='SYS' AND ora_dict_obj_owner!='SYSTEM')
DECLARE
	l_sysevent	VARCHAR2(25);
        sql_text        ora_name_list_t;
        id              INTEGER;
        n               INTEGER;
        ac              INTEGER:=0;
        dc              INTEGER:=0;
BEGIN
        SELECT ora_sysevent INTO l_sysevent FROM DUAL;
        SELECT oradev.ddltrace_seq.NEXTVAL INTO id FROM DUAL;
        INSERT INTO oradev.ddltrace(id,current_scn,obj_owner,obj_name,obj_type,operation)
        SELECT  id,current_scn, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, l_sysevent
        FROM    v$database;

        n:= ora_sql_txt(sql_text);
        FOR i IN 1..n LOOP
                INSERT INTO oradev.ddltrace_stmnt (id,ddl_id,ddl_line,ddl_text)
                VALUES (oradev.ddltrace_stmnt_seq.NEXTVAL,id, i, sql_text(i));
        END LOOP;
        IF (ora_sysevent = 'ALTER' AND ora_dict_obj_type = 'TABLE')
        THEN
                FOR col IN (
                        SELECT  column_name,column_id
                        FROM    all_tab_columns
                        WHERE   owner=ora_dict_obj_owner
                          AND   table_name=ora_dict_obj_name
                ) LOOP
                        IF (ora_is_alter_column(col.column_name))
                        THEN
                                ac:=1;
                        END IF;
                        IF (ora_is_drop_column(col.column_name))
                        THEN
                                dc:=1;
                        END IF;
                        INSERT INTO oradev.ddltrace_cols (id,ddl_id,ddl_col_name,is_altered,is_deleted)
                        VALUES (oradev.ddltrace_cols_seq.NEXTVAL,id, col.column_name,ac, dc);
                END LOOP;
        END IF;
END;
/

